package com.example.sql_valid.service;

import com.example.sql_valid.constant.Constant;
import com.example.sql_valid.controller.Response.ValidationResponse;
import com.example.sql_valid.controller.SQLValidatorController;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.index.CreateIndex;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.merge.Merge;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 *  SQL验证服务类，用于验证给定的SQL文件列表是否符合指定的schema规范
 */
@Service
public class SQLValidatorServiceV1 {

    private static final Logger logger = LoggerFactory.getLogger(SQLValidatorController.class);


    /**
     * 验证给定的SQL文件列表是否符合指定的schema规范
     *
     * @param files           待验证的SQL文件列表
     * @param schema          指定的schema名称
     * @param checkTablespace 是否检查表空间
     * @param isOB            是否处理Oracle数据库的ENABLE关键字
     * @return 包含验证结果和错误文件列表的ValidationResponse对象
     */
    public ValidationResponse  SchemaValid(List<MultipartFile> files, String schema, boolean checkTablespace,boolean isOB) {
        StringBuilder result = new StringBuilder();
        //文件列表
        List<String> errorFiles = new ArrayList<>();

        for (MultipartFile file : files) {
            String fileName = file.getOriginalFilename();
            StringBuilder fileErrors = new StringBuilder();
            try {
                List<String> lines = readLines(file);
                String rawSQL = String.join("\n", lines);

                String cleanSQL = removeComments(rawSQL);

                List<String> sqlStatements = splitSQLStatements(cleanSQL);

                for (String sqlStatementOld : sqlStatements) {
                    try {
                        String sqlStatement = sqlStatementOld;
                        if(isOB && sqlStatement.toLowerCase().contains("create table")) {
                            sqlStatement = sqlStatementOld.replace("ENABLE", "");
                        }
                        Statement statement = CCJSqlParserUtil.parse(sqlStatement);
                        boolean schemaMis = false;
                        boolean schemaNotMatch = false;
                        boolean missingTablespace = false;

                        if (statement instanceof Select) {
                            Select select = (Select) statement;
                            Map<String, Boolean> map = findTable(select, schema);
                            schemaMis = map.get("schemaMis");
                            schemaNotMatch = map.get("schemaNotMatch");
                            //处理create表语句
                        } else if (statement instanceof CreateTable) {
                            CreateTable createTable = (CreateTable) statement;
                            Map<String, Boolean> map = findTable(createTable, schema);
                            schemaMis = map.get("schemaMis");
                            schemaNotMatch = map.get("schemaNotMatch");
                            missingTablespace = vaildTableSpace(checkTablespace, sqlStatement);

                            //处理createIndex表语句
                        } else if (statement instanceof CreateIndex) {
                            CreateIndex createIndex = (CreateIndex) statement;
                            //获取索引名，带schema
                            String name = createIndex.getIndex().getName();
                            //分割出schema
                            String[] schemaNames = name.split("\\.");

                            if (schemaNames.length <= 1) {
                                schemaMis = true;
                            } else {
                                String schemaName = schemaNames[0].replace("\"", "");
                                if (!schemaName.equalsIgnoreCase(schema)) {
                                    schemaNotMatch = true;
                                }
                            }
//                            if (checkTablespace && !tablespacePattern.matcher(sqlStatement).find()) {
//                                missingTablespace = true;
//                            }
                            missingTablespace = vaildTableSpace(checkTablespace, sqlStatement);
                        } else if (statement instanceof Update) {
                            Update update = (Update) statement;

                            Map<String, Boolean> map = findTable(update, schema);
                            schemaMis = map.get("schemaMis");
                            schemaNotMatch = map.get("schemaNotMatch");

                        } else if (statement instanceof Delete) {
                            Delete delete = (Delete) statement;

                            Map<String, Boolean> map = findTable(delete, schema);
                            schemaMis = map.get("schemaMis");
                            schemaNotMatch = map.get("schemaNotMatch");

                        } else if (statement instanceof Insert) {
                            Insert insert = (Insert) statement;

                            Map<String, Boolean> map = findTable(insert, schema);
                            schemaMis = map.get("schemaMis");
                            schemaNotMatch = map.get("schemaNotMatch");

                        } else if (statement instanceof Drop) {
                            Drop drop = (Drop) statement;
                            Map<String, Boolean> map = findTable(drop, schema);
                            schemaMis = map.get("schemaMis");
                            schemaNotMatch = map.get("schemaNotMatch");
                        } else if (statement instanceof Alter) {
                            Alter alter = (Alter) statement;
                            String tableName = alter.getTable().toString();

                            String[] schemaNames = tableName.split("\\.");

                            if (schemaNames.length <= 1) {
                                schemaMis = true;
                            } else {
                                String schemaName = schemaNames[0].replace("\"", "");
                                if (!schemaName.equalsIgnoreCase(schema)) {
                                    schemaNotMatch = true;
                                }
                            }
                        } else if (statement instanceof CreateView) {
                            String fullyQualifiedName = ((CreateView) statement).getView().getFullyQualifiedName();

                            String[] schemaNames = fullyQualifiedName.split("\\.");

                            if (schemaNames.length <= 1) {
                                schemaMis = true;
                            } else {
                                String schemaName = schemaNames[0].replace("\"", "");
                                if (!schemaName.equalsIgnoreCase(schema)) {
                                    schemaNotMatch = true;
                                }
                            }
                            Select select = ((CreateView) statement).getSelect();
                            Map<String, Boolean> map = findTable(select, schema);
                            if (schemaMis != true) {
                                schemaMis = map.get("schemaMis");
                            }
                            if (schemaNotMatch != true) {
                                schemaNotMatch = map.get("schemaNotMatch");
                            }

                        }else if(statement instanceof Merge){
                            Merge merge = (Merge) statement;
                            Map<String, Boolean> map = findTable(merge, schema);
                            schemaMis = map.get("schemaMis");
                            schemaNotMatch = map.get("schemaNotMatch");

                        }
//                            CreateView createView = (CreateView) statement;
//                            Map<String, Boolean> map = findTable(createView, schema);
//                            schemaMis = map.get("schemaMis");
//                            schemaNotMatch = map.get("schemaNotMatch");

                        if (schemaNotMatch) {
                            fileErrors.append("文件: ").append(fileName).append(" - Schema 错误，语句: ").append("\n").append(sqlStatementOld).append("\n");
                        }
                        if (schemaMis) {
                            fileErrors.append("文件: ").append(fileName).append(" - Schema 缺失，语句: ").append("\n").append(sqlStatementOld).append("\n");
                        }
                        if (missingTablespace) {
                            fileErrors.append("文件: ").append(fileName).append(" - 缺少表空间语句，语句: ").append("\n").append(sqlStatementOld).append("\n");
                        }
                    } catch (JSQLParserException e) {
                        fileErrors.append("文件: ").append(fileName).append(" - SQL解析失败，语句: ").append(sqlStatementOld).append("\n");
                        logger.error("SQL解析失败: " + sqlStatementOld, e);
                    }
                }

                if (fileErrors.length() == 0) {
                    result.append("文件: ").append(fileName).append(" - 验证通过\n");
                }

            } catch (IOException e) {
                fileErrors.append("文件: ").append(fileName).append(" - 读取失败\n");
            }

            if (fileErrors.length() > 0) {
                result.append(fileErrors);
                try {
                    Path errorFilePath = Paths.get(Constant.errorFiles, fileName.replace(".sql", "_error.sql"));
                    Files.createDirectories(errorFilePath.getParent());

                    Files.write(errorFilePath, fileErrors.toString().getBytes(StandardCharsets.UTF_8));
                    errorFiles.add(errorFilePath.getFileName().toString());  // 添加文件名到列表
//                    result.append("错误SQL已保存到: ").append(errorFilePath.toAbsolutePath()).append("\n");
                    logger.info("错误SQL已保存到: " + errorFilePath.toAbsolutePath());
                } catch (IOException e) {
                    result.append("保存错误文件时出错: ").append(e.getMessage()).append("\n");
                    logger.error("保存错误文件时出错: " + e.getMessage(), e);
                }
            }
        }

        ValidationResponse response = new ValidationResponse();
        response.setResult(result.toString());
        response.setErrorFiles(errorFiles);

        return response;
    }

    private List<String> readLines(MultipartFile file) throws IOException {
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream(), StandardCharsets.UTF_8))) {
            return reader.lines().collect(Collectors.toList());
        }
    }

    private String removeComments(String sql) {
        // 移除单行注释
        sql = sql.replaceAll("--.*?(\r?\n|$)", "");
        // 移除多行注释

        // 移除多行注释，使用 DOTALL 模式
        sql = sql.replaceAll("(?s)/\\*.*?\\*/", "");
        return sql;
    }

    private List<String> splitSQLStatements(String sql) {
        // 使用分号加换行符分割SQL语句
        return Arrays.asList(sql.split(";\\s*\\n"));
    }

    private Map<String, Boolean> findTable(Statement statement, String schema) {
        Boolean schemaMis = false;
        Boolean schemaNotMatch = false;

        Map<String, Boolean> stringBooleanHashMap = new HashMap<>();

        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();

        List<String> tableList = tablesNamesFinder.getTableList(statement);

        for (String s : tableList) {
            String[] schemaNames = s.split("\\.");

            if (schemaNames.length <= 1) {
                schemaMis = true;
            } else {
                String schemaName = schemaNames[0].replace("\"", "");
                if (!schemaName.equalsIgnoreCase(schema)) {
                    schemaNotMatch = true;
                }
            }
        }

        stringBooleanHashMap.put("schemaMis", schemaMis);
        stringBooleanHashMap.put("schemaNotMatch", schemaNotMatch);

        return stringBooleanHashMap;

    }

    private Boolean vaildTableSpace(boolean checkTablespace, String sqlStatement) {
        Boolean missingTablespace = false;

        Pattern tablespacePattern = Pattern.compile("\\btablespace\\b", Pattern.CASE_INSENSITIVE);
        if (checkTablespace && !tablespacePattern.matcher(sqlStatement).find()) {
            missingTablespace = true;
        }

        return missingTablespace;
    }
}
